package myJlite.dao;

import java.sql.SQLException;
import java.util.List;

import myJlite.entity.ExamVO;
import myJlite.entity.RoleVO;

import jvlite.dao.JliteORM.BeanHandler;
import jvlite.dao.JliteORM.BeanListHandler;
import jvlite.dao.JliteORM.JliteJdbcSupport;
import jvlite.mvc.annotaion.MethodAuth;
import jvlite.mvc.annotaion.PointCut;
import jvlite.util.StrUtil;

@PointCut(advice="examAdvice", value = "")
public class ExamDAO extends JliteJdbcSupport implements IExamDAO {
	
	@MethodAuth(requirePower={"admin"})
	public Integer add(ExamVO exam) throws SQLException {
		String sql = "insert into exam(username,marks,password,sex) values(?,?,?,?)";
		Object[] params = { exam.getUsername(), exam.getMarks(), exam.getPassword(), exam.getSex() };
		return update(sql, params);
	}

	public Integer delete(int id) throws SQLException {
		String sql = "delete from exam where id=?";
		Object[] params = { id };
		return update(sql, params);
	}

	public Integer update(ExamVO exam) throws SQLException {
		String sql = "update exam set username=?,marks=? where id=?";
		Object[] params = { exam.getUsername(), exam.getMarks(), exam.getId() };
		return update(sql, params);
	}

	public ExamVO getByID(Long id) throws SQLException {
		String sql = "select * from exam where id=?";
		Object[] params = { id };
		return (ExamVO) query(sql, params, new BeanHandler(
				ExamVO.class));
	}
	
	public ExamVO getByVO(ExamVO examVO) throws SQLException{
		StringBuffer sql = new StringBuffer("select * from exam where 1=1 ");
		if (examVO.getId()!=null) {
			sql.append(" and id="+examVO.getId().toString());
		}
		if (!StrUtil.isBlank(examVO.getUsername())) {
			sql.append(" and username like '%"+examVO.getUsername()+"%'");
		}
		if (examVO.getMarks()!=null) {
			sql.append(" and marks="+examVO.getMarks().toString());
		}
		return (ExamVO)queryBySQL(sql.toString(), new BeanHandler(ExamVO.class));
/*		ExamVO vo = new ExamVO();
		vo.setId(9L);
		return vo;*/
	}

	public List<?> getAll() throws SQLException {
		String sql = "select * from exam";
		Object[] params = {};
		return (List<?>) query(sql, params, new BeanListHandler(
				ExamVO.class));
	}
	
	public RoleVO fetchAuth(ExamVO examVO) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT r.role_auths FROM exam e, roles r WHERE e.role_id=r.role_id ");
		if (examVO.getId()!=null) {
			sql.append(" and id="+examVO.getId().toString());
		}
		if (!StrUtil.isBlank(examVO.getUsername())) {
			sql.append(" and username like '%"+examVO.getUsername()+"%'");
		}
		if (examVO.getMarks()!=null) {
			sql.append(" and marks="+examVO.getMarks().toString());
		}
		return (RoleVO)queryBySQL(sql.toString(), new BeanHandler(RoleVO.class));
	}
	
}